package by.epam.communalservices.utils;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.entity.JkhTask;
import by.epam.communalservices.exception.JkhDaoException;

public class Job {

    private Connection con;

    private final String COLUMN_TASK_ID = "task_id";

    private final String COLUMN_OBJECT_ID = "jkh_object_id";

    private final String COLUMN_TEAM_ID = "team_id";

    private final String COLUMN_DESCRIPTION = "description";

    private final String COLUMN_HOURS = "hours";

    private final String COLUMN_STATE = "state";
    
    private final String COLUMN_REQUEST = "request";

    static Logger JdbcDaoJobLoger = Logger.getLogger(Job.class.getName());

    public Job(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException("Connection error. Connection is not open", new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error", new Exception());

        }
    }

    public List<JkhTask> listTeamTask(Long teamId) {

        JdbcDaoJobLoger.debug("List<JkhTask> listTeamTask(Long teamId)");
        JdbcDaoJobLoger.info("method returns a list of task");
        @SuppressWarnings("unchecked")
        List<JkhTask> list = Collections.EMPTY_LIST;
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT * FROM public.jkh_task  WHERE team_id = ? ORDER BY state";
                try {
                    pstate = con.prepareStatement(sql);
                    pstate.setLong(1, teamId);
                    result = pstate.executeQuery();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("query execute error", ex);
                    ex.printStackTrace();
                }
                list = new ArrayList<JkhTask>();
                while (result.next()) {
                    JkhTask jkhTask = new JkhTask(
                            result.getLong(COLUMN_TASK_ID), 
                            result.getLong(COLUMN_OBJECT_ID),
                            result.getLong(COLUMN_TEAM_ID), 
                            result.getString(COLUMN_DESCRIPTION),
                            result.getDouble(COLUMN_HOURS), 
                            result.getString(COLUMN_STATE),
                            result.getString(COLUMN_REQUEST));
                    list.add(jkhTask);
                }

            } catch (SQLException ex) {
                JdbcDaoJobLoger.error("Error compiling the list", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }

    public List<JkhTask> listObjectTask(Long objectId) {

        JdbcDaoJobLoger.debug("List<JkhTask> listObjectTask(objectId="+objectId+")!!!!!!!!!");
        JdbcDaoJobLoger.info("method returns a list of task");
        @SuppressWarnings("unchecked")
        List<JkhTask> list = Collections.EMPTY_LIST;
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT * FROM public.jkh_task  WHERE jkh_object_id = ? ORDER BY state";
                try {
                    pstate = con.prepareStatement(sql);
                    pstate.setLong(1, objectId);
                    result = pstate.executeQuery();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("query execute error", ex);
                    ex.printStackTrace();
                }
                list = new ArrayList<JkhTask>();
                while (result.next()) {
                    JkhTask jkhTask = new JkhTask(
                            result.getLong(COLUMN_TASK_ID), 
                            result.getLong(COLUMN_OBJECT_ID),
                            result.getLong(COLUMN_TEAM_ID), 
                            result.getString(COLUMN_DESCRIPTION),
                            result.getDouble(COLUMN_HOURS), 
                            result.getString(COLUMN_STATE),
                            result.getString(COLUMN_REQUEST));
                    list.add(jkhTask);
                }

            } catch (SQLException ex) {
                JdbcDaoJobLoger.error("Error compiling the list", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoJobLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }

}
